package com.gugee.ins.common.dal.mapper.blogger;

import com.gugee.common.mapper.GugeeMapper;
import com.gugee.ins.common.model.ai.InComeRequest;
import com.gugee.ins.common.model.mysql.blogger.InsBlogger;
import com.gugee.ins.common.model.mysql.blogger.InsBloggerMain;
import com.gugee.ins.data.common.msg.InsBloggerMessage;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface InsBloggerMainMapper extends GugeeMapper<InsBloggerMain> {

    @Select("select max(id) from ins_blogger_main ")
    long getMaxId();
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} and is_private = 0 ")
    List<InsBloggerMessage> findInsBloggerMessageByPagesId(long startId,long endId);
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} AND follower_count >= #{followerCount} AND last_collect_time < #{lastCollectTime} and account_status = 0 ")
    List<InsBloggerMessage> findInsBloggerMessageByPages(long startId,long endId,long followerCount,long lastCollectTime);

    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} and follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsBloggerMessageByFollowerCountAndPages(long startId,long endId, long followerCount);


    @Select("select m.uid,m.id from ins_blogger_main m where m.id between #{startId} and #{endId} " +
            " and m.follower_count >= #{followerCount} and m.last_real_follower_st_time < #{limitTime} and m.account_status = 0 ")
    List<InsBloggerMessage> findInsBloggerMessageByRealFollowerStTime(long startId,long endId,long followerCount,long limitTime);


    @Select("select m.uid,m.id from ins_blogger_main m where m.id between #{startId} and #{endId} " +
            " and m.follower_count >= #{followerCount} and m.account_status = 0 and (m.last_gender_st_time < #{limitTime} or m.last_nation_st_time < #{limitTime}" +
            " or m.last_language_st_time < #{limitTime} or m.last_brand_st_time < #{limitTime})")
    List<InsBloggerMessage> findInsBloggerMessageByLastGenderStTime(long startId,long endId,long followerCount,long limitTime);

    /**
     * 根据uid查找数据
     * @param uid
     * @return
     */
    @Select("select * from ins_blogger_main where uid = #{uid}")
    InsBloggerMain findByUid(long uid);

    @Select({"<script>","select * from ins_blogger_main where uid in ",
            "<foreach item='uid' index='index' collection='uids'  open='(' separator=',' close=')'>",
                    "#{uid}",
                    "</foreach>","</script>"})
    List<InsBloggerMain> findInUid(List<Long> uids);


    @Insert("<script>" +
                " INSERT INTO ins_blogger_main(uid,username,full_name,biography,pic_url,external_url,following_count,follower_count,follower_size " +
                ",media_count,igtv_count,following_tag_count,usertags_count,is_private,is_verified,is_business,is_zombie,is_massive_following,account_type " +
                ",category_name,overall_category_name,fbid,contact_phone_number,public_email,public_phone_country_code,public_phone_number,city_id,city_name,create_time) " +
                " SELECT uid,username,full_name,biography,pic_url,external_url,following_count,follower_count," +
            "(select b.id from ins_dic_follower_size b where a.follower_count between size_start and size_end) follower_size  " +
                ",media_count,igtv_count,following_tag_count,usertags_count,is_private,is_verified,is_business,is_zombie,is_massive_following,account_type  " +
                ",category_name,overall_category_name,fbid,contact_phone_number,public_email,public_phone_country_code,public_phone_number,city_id,city_name,#{createTime}  " +
                "FROM ins_blogger a WHERE a.uid  IN " +
                     "<foreach item='uid' index='index' collection='uidS'  open='(' separator=',' close=')'>" +
                         "#{uid}" +
                     "</foreach>" +
                " AND follower_count>= #{followerCount} AND NOT EXISTS (select 1 from ins_blogger_main b where b.uid =a.uid )" +
             "</script>")
     void collectMBloggerFromUidS(List<Long> uidS,int followerCount,long createTime) ;

    /**
     * 统计同量级播主的真实粉丝平均率
     * @param category
     * @param followerStart
     * @param followerEnd
     * @return
     */
    @Select("SELECT IFNULL(AVG(real_follower_rate),0)  FROM ins_blogger_main WHERE overall_category_name = #{category} AND follower_count BETWEEN #{followerStart} AND #{followerEnd}")
    double stBMAvgRealFollowerRateByCategoryAndFollower(String category, int followerStart, int followerEnd);

    @Select("SELECT IFNULL(AVG(last_follower_grow_rate),0)  FROM ins_blogger_main WHERE overall_category_name = #{category} AND follower_count BETWEEN #{followerStart} AND #{followerEnd}")
    double stBMAvgFollowerGrowRateByCategoryAndFollower(String category, int followerStart, int followerEnd);


    /**
     * 统计同量级播主的帖子参与率
     * @param category
     * @param followerStart
     * @param followerEnd
     * @return
     */
    @Select({"<script>",
            "select IFNULL(SUM(im.er),0) FROM ins_blogger_main bm LEFT JOIN ins_media im ON bm.uid = im.owner_uid WHERE " +
            "bm.follower_count BETWEEN #{followerStart} AND #{followerEnd} AND bm.overall_category_name = #{category}  AND taken_time > #{timeRang} ",
            "<when test='null != isAd'>",
            "AND im.is_ad = #{isAd}",
            "</when>",
            "</script>"})
    double stBMSumMediaErByCategoryAndFollower(String category, int followerStart, int followerEnd,long timeRang,Boolean isAd);

    @Select("select count(*) FROM ins_blogger_main bm WHERE " +
            " bm.follower_count BETWEEN #{followerStart} AND #{followerEnd} and bm.overall_category_name = #{category}  ")
    long countByCategoryAndFollower(String category, int followerStart, int followerEnd);

    /**
     * 获取需要采集帖子的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @param mediaMinCount
     * @param lastMediaCollectTime
     * @return
     */
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} " +
            " and is_private = 0 and media_count > #{mediaMinCount} " +
            " AND follower_count >= #{followerCount} AND last_media_collect_time < #{lastMediaCollectTime} and account_status = 0 ")
    List<InsBloggerMessage> findInsMediaCollectMessageByPages(long startId, long endId,long followerCount,
                                                              int mediaMinCount, long lastMediaCollectTime);

    /**
     * 获取需要采集评论的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @param lastCommentCollectTime
     * @return
     */
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} and is_private = 0 " +
            " AND follower_count >= #{followerCount} AND last_comment_collect_time < #{lastCommentCollectTime} and account_status = 0 ")
    List<InsBloggerMessage> findInsCommentCollectMessageByPages(long startId, long endId,long followerCount,
                                                              long lastCommentCollectTime);

    /**
     * 获取需要采集粉丝的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @param lastFollowerCollectTime
     * @return
     */
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} and is_private = 0 " +
            " AND follower_count >= #{followerCount} AND last_follower_collect_time < #{lastFollowerCollectTime} and account_status = 0  ")
    List<InsBloggerMessage> findInsBloggerFollowerCollectMessageByPages(long startId, long endId,long followerCount,
                                                                long lastFollowerCollectTime);

    /**
     * 获取需要分析的帖子消息
     * @param startId
     * @param endId
     * @param followerCount
     * @param mediaMinCount
     * @return
     */
    @Select("select uid,id  from ins_blogger_main where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and media_count > #{mediaMinCount} and account_status = 0 ")
    List<InsBloggerMessage> findAdMediaIdentifyByPages(long startId, long endId, long followerCount,
                                                       int mediaMinCount);

    /**
     * 获取需要采集地理位置的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid,case when city_name is null and detail_city_name is null and is_private = 0 then 1 else 2 end type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsLocationCollectMessageByPages(long startId, long endId,long followerCount);

    /**
     * 获取需要采集地理位置的大播主活跃粉丝消息数据
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select t1.f_uid uid,t2.media_count,t2.follower_count,t2.last_location_collect_time from " +
            " (select distinct f_uid from ins_rel_blogger_active_follower where uid = #{uid}) t1 " +
            " inner join ins_blogger t2 on t1.f_uid = t2.uid " +
            " where t2.city_name is null and t2.detail_city_name is null " +
            " and t2.is_private = 0 and t2.last_location_collect_time < #{limitTime}")
    List<InsBlogger> findInsLocationCollectMessageByUid(long uid, long limitTime);

    /**
     * 获取需要采集关注的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid, 2 type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsBloggerFollowingCollectMessageByPages(long startId, long endId,long followerCount);

    /**
     * 获取需要采集关注的大播主活跃粉丝消息数据
     * @param uid
     * @param limitTime
     * @param followingMinCount
     * @param limitCount
     * @return
     */
    @Select("select t1.f_uid uid from " +
            " (select distinct f_uid from ins_rel_blogger_active_follower where uid = #{uid}) t1 " +
            " inner join ins_blogger t2 on t1.f_uid = t2.uid " +
            " where t2.is_private = 0 and t2.last_following_collect_time < #{limitTime} " +
            " and (t2.following_count > #{followingMinCount} or t2.last_collect_time = 0) limit 0,#{limitCount} ")
    List<InsBloggerMessage> findInsBloggerFollowingCollectMessageByUid(long uid,long limitTime,int followingMinCount,int limitCount);

    /**
     * 获取需要分析头像的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid, pic_url,case when gender != 2 and gender != 3 and is_anonymous_pic = 0 then 1 else 2 end type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsAnalysisAvatarMessageByPages(long startId, long endId,long followerCount);

    /**
     * 获取需要分析头像的大播主活跃粉丝消息数据
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select t2.uid,t2.pic_url from " +
            " (select distinct f_uid from ins_rel_blogger_active_follower where uid = #{uid}) t1 " +
            " inner join ins_blogger t2 on t1.f_uid = t2.uid " +
            " where t2.gender = 0 and t2.is_anonymous_pic = 0 and t2.last_gender_analysis_time < #{limitTime} ")
    List<InsBloggerMessage> findInsAnalysisAvatarMessageByUid(long uid,long limitTime);

    /**
     * 获取需要分析语言的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid,case when `language` is null or `language` = 'un' then 1 else 2 end type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsAnalysisLanguageMessageByPages(long startId, long endId,long followerCount);

    /**
     * 获取需要分析国家的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid,case when nation is null or nation = '' then 1 else 2 end type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0 ")
    List<InsBloggerMessage> findInsAnalysisNationMessageByPages(long startId, long endId,long followerCount);

    /**
     * 获取需要分析国家的的大播主活跃粉丝消息数据
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select t2.uid from " +
            " (select distinct f_uid from ins_rel_blogger_active_follower where uid = #{uid}) t1 " +
            " inner join ins_blogger t2 on t1.f_uid = t2.uid " +
            " where (t2.nation is null or t2.nation = '') and t2.last_gender_analysis_time < #{limitTime} ")
    List<InsBloggerMessage> findInsAnalysisNationMessageByUid(long uid,long limitTime);

    /**
     * 获取指定的粉丝量级id信息
     * @param idLimit
     * @return
     */
    @Select("select id from ins_dic_follower_size where id < #{idLimit}")
    List<Integer> findFollowerSizeIdByLimitId(int idLimit);

    /**
     * 获取同分类同量级的播主总数
     * @param category
     * @param followerSize
     * @return
     */
    @Select("select count(1) from ins_blogger_main where follower_size = #{followerSize} and overall_category_name = #{category} and account_status = 0  ")
    int findTotalCountByCategoryAndFollowerSize(String category,int followerSize);

    /**
     * 获取需要分析收入的大播主活跃粉丝消息数据
     * @param uid
     * @param limitTime
     * @return
     */
    @Select("select t2.uid id,t2.nation country,null city,case when t2.age <= 17 then null when t2.age < 25 then 0 when t2.age < 35 then 1 else 2 end age ," +
            " case when t2.gender = 2 then 1 when t2.gender = 3 then 0 else null end gender from " +
            " (select distinct f_uid from ins_rel_blogger_active_follower where uid = #{uid}) t1 " +
            " inner join ins_blogger t2 on t1.f_uid = t2.uid  " +
            "where t2.income is null and t2.nation != '' and t2.last_income_analysis_time < #{limitTime} ")
    List<InComeRequest> findInsAnalysisIncomeByUid(long uid, long limitTime);

    /**
     * 获取需要分析收入的大播主消息数据
     * @param uid
     * @return
     */
    @Select(" select t2.uid id,t2.nation country,null city,case when t2.age <= 17 then null when t2.age < 25 then 0 when t2.age < 35 then 1 else 2 end age ," +
            " case when t2.gender = 2 then 1 when t2.gender = 3 then 0 else null end gender from " +
            " ins_blogger_main t2 where t2.uid = #{uid} and t2.income is null and t2.nation != '' ")
    List<InComeRequest> findMInsAnalysisIncomeByUid(long uid);

    /**
     * 获取需要分析收入的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @return
     */
    @Select("select uid,case when income is null and nation != '' then 1 else 2 end type_code from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} and account_status = 0  ")
    List<InsBloggerMessage> findInsAnalysisIncomeMessageByPages(long startId, long endId,long followerCount);

    /**
     * 根据给定的uid 查找出未进行播主分类的uid
     * @param insBloggerMessages
     * @return
     */
    @Select("<script> select uid from ins_blogger_main where uid in " +
            " <foreach item='i' index='index' collection='insBloggerMessages'  open='(' separator=',' close=')'>" +
            " #{i.uid} " +
            " </foreach>" +
            " and (overall_category_name = '' or overall_category_name is null ) " +
            "</script>")
    List<Long> findEmptyCategoryByUid(@Param("insBloggerMessages") List<InsBloggerMessage> insBloggerMessages);

    /**
     * 获取需要分析分类的播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @param limitTime
     * @return
     */
    @Select("select id, uid from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{followerCount} " +
            " AND (overall_category_name = '' or overall_category_name is null )" +
            " AND last_ai_analysis_category_time < #{limitTime}" +
            " AND last_media_collect_time > 0  and account_status = 0 ")
    List<InsBloggerMessage> findInsAnalysisCategoryMessageByPages(long startId, long endId,long followerCount,long limitTime);

    /**
     * 获取需要更新数据的掉分的大播主消息数据
     * @param startId
     * @param endId
     * @param followerCount
     * @param limitTime
     * @return
     */
    @Select("select id, uid from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count < #{followerCount} " +
            " AND last_collect_time < #{limitTime} and account_status = 0 ")
    List<InsBloggerMessage> findUpdateLowFollowerMBloggerMsgByPages(long startId, long endId,long followerCount,long limitTime);

    /**
     * 查看是否存在
     * @param uid
     * @return
     */
    @Select("select 1 from ins_blogger_main where uid = #{uid}")
    Integer findExist(long uid);

    /**
     * 获取需要更新数据的中播主消息数据
     * @param startId
     * @param endId
     * @param mainBloggerFollowerCount
     * @param bigBloggerFollowerCount
     * @param limitTime
     * @return
     */
    @Select("select id, uid from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " AND follower_count >= #{mainBloggerFollowerCount} AND follower_count < #{bigBloggerFollowerCount} " +
            " AND last_collect_time < #{limitTime} and account_status = 0 ")
    List<InsBloggerMessage> findUpdateMiddleBloggerMsgByPages(long startId, long endId, long mainBloggerFollowerCount,
                                                              long bigBloggerFollowerCount, long limitTime);

    /**
     * 获取需要采集帖子的中播主消息数据
     * @param startId
     * @param endId
     * @param mainBloggerFollowerCount
     * @param bigBloggerFollowerCount
     * @param mediaMinCount
     * @param lastMediaCollectTime
     * @return
     */
    @Select("select id, uid from ins_blogger_main " +
            " where id between #{startId} and #{endId} " +
            " and is_private = 0 and media_count > #{mediaMinCount} " +
            " AND follower_count >= #{mainBloggerFollowerCount} AND follower_count < #{bigBloggerFollowerCount} " +
            " AND last_media_collect_time < #{lastMediaCollectTime} and account_status = 0 ")
    List<InsBloggerMessage> findInsMiddleBgMediaCollectMessageByPages(long startId, long endId,long mainBloggerFollowerCount,
                                                                      long bigBloggerFollowerCount,int mediaMinCount,
                                                                      long lastMediaCollectTime);

    /**
     * 查询需要分析品牌的播主信息
     * @param startId
     * @param endId
     * @param limitTime
     * @return
     */
    @Select("select id,uid,CONCAT_WS(',',username,full_name,biography) username " +
            "from ins_blogger_main " +
            "where id between #{startId} and #{endId} and is_verified = 1 " +
            "and is_business = 1 and account_status = 0 " +
            "and manual_review_brand = -1 " +
            "and last_ai_brand_analysis_time < #{limitTime} ")
    List<InsBloggerMessage> findAnalysisBrandBloggerByPages(long startId, long endId, long limitTime);

    /**
     * 根据uid 获取 userName
     * @param uids
     * @return
     */
    @Select("<script> select username from ins_blogger_main where uid in " +
            " <foreach item='uid' index='index' collection='uids'  open='(' separator=',' close=')'>" +
            " #{uid} " +
            " </foreach>" +
            "</script>")
    List<String> findUserNameByUid(@Param("uids")List<Long> uids);
}